﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using BCDataCenter.DataAccessLayer;
/// <summary>
/// Summary description for BWNhapHoaDonBanHang
/// </summary>
public class BWNhapHoaDonBanHang
{
    string SqlGetListMatHang = "get_ListMatHang";
    string SqlGetFilterListMatHang = "get_FilterListMatHang";
    string SqlGetFilterListTuVanVien = "get_FilterListTuVanVien";
    string SqlGetKH = "k_getFilterListKH";
    string sqlGetFilterListLoaiDong = "get_FilterListLoaiDong";
    string SqlGetInfoTuVanVien = "get_InfoTuVanVien";
    string SqlGetMaHD = "get_MaHD";
    string SqlGetDoanhSoTrongThangTVV = "get_DoanhSoTrongThangCuaTVV";
    string SqlGetPriceTaxMH = "get_InfoMatHang";
    string SqlGetHoDonChuaIn = "get_HDChuaIn";
    string SqlGetHoDonChuaInTVV = "get_HDChuaInTVV";
    string SqlGetInfoHoaDonChuaIn = "get_InfoHDChuaIn";

    string SqlInsertHDBanHangTVV = "Insert_HoaDonBanHangTVV";
    string SqlInsertHDUngHangChoNhomTruong = "Insert_HoaDonUngHangChoNhomTruong";
    string SqlInsertCTHDBanHangTVV = "Insert_CTHoaDonBanHangTVV";
    string SqlDeleteCTHDBanHangTVV = "Delete_CTHoaDonBanHangTVV";
    string SqlDeleteHDBanHangTVV = "Delete_HoaDonBanHangTVV";
    string SqlDeleteHDvaCTHD = "delete_HoaDonTVV";
    string SqlInsertUserInput = "Insert_USER_INPUT";

    string SqlUpdateHDTVV = "Update_HoaDonTVV";

    string SqlGetLocation = "get_Location";
    string SqlGetVuotTon = "In_CheckIsOverQty";
    string SqlCheckMaHD = "check_MaHoaDon";
    string sqlGetMinDay = "get_CloseDate";
    string SqlGetListHangKhuyenMai = "get_ListHangKhuyenMai";
    string sqlGetChiTietDonHang = "get_ChiTietHoaDon";
    string sqlGetChiTietDonHangPrint = "get_ChiTietHoaDon_Print";

    string sqlCheckIsExistKhuyenMai = "checkExist_ChuongTrinhKhuyenMai";
    string sqlGetMatHang = "get_MatHang";
    string sqlUpdateHDChuaIn = "Update_HDChuaIn";
    

    public BWNhapHoaDonBanHang()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public DataSet dsListMatHang(ref string error)
    { 
        return new DAL().MyExecuteReader(ref error, SqlGetListMatHang, CommandType.StoredProcedure);
    }

    public DataSet dsFilterListMatHang(ref string error, string filter)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetFilterListMatHang, CommandType.StoredProcedure, new SqlParameter("@filter", filter));
    }
    public DataSet dsFilterListLoaiDong(ref string error, string filter, string NgayHD)
    {
        return new DAL().MyExecuteQuery(ref error, sqlGetFilterListLoaiDong, CommandType.StoredProcedure, new SqlParameter("@filter", filter), new SqlParameter("@NgayHD", NgayHD));
    }

    public DataSet dsFilterListTuVanVien(ref string error, string filter)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetFilterListTuVanVien, CommandType.StoredProcedure, new SqlParameter("@filter", filter));
    }
    public DataSet dsFilterListKH(ref string error, string filter)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetKH, CommandType.StoredProcedure, new SqlParameter("@filter", filter));
    }

    public DataSet dsInfoTuVanVien(ref string error, string MaTVV)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetInfoTuVanVien, CommandType.StoredProcedure, new SqlParameter("@MaTVV", MaTVV));
    }
    public DataSet dsMatHD(ref string error)
    {
        return new DAL().MyExecuteReader(ref error, SqlGetMaHD, CommandType.StoredProcedure);
    }

    public DataSet dsDoanhSoTrongThangTVV(ref string error, string MaTVV)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetDoanhSoTrongThangTVV, CommandType.StoredProcedure, new SqlParameter("@MaTVV", MaTVV));
    }

    public DataSet dsGia_ThueMH(ref string error, string MaMH)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetPriceTaxMH, CommandType.StoredProcedure, new SqlParameter("@MaMH", MaMH));
    }
    public DataSet dsHoaDonChuaIn(ref string error, string Sitecode, int So)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetHoDonChuaIn, CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode), new SqlParameter("@so", So));
    }
    public DataSet dsHoaDonTraHangChuaIn(ref string error, string Sitecode, int So)
    {
        return new DAL().MyExecuteQuery(ref error, "get_HDTraHangChuaIn", CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode), new SqlParameter("@so", So));
    }

    public DataSet dsFindHoaDonTVV(ref string error, string Sitecode, string MaTVV, int So, string Loai, string TuNgay, string DenNgay)
    {
        return new DAL().MyExecuteQuery(ref error, "find_HoaDonTVV", CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode), new SqlParameter("@MaTVV", MaTVV), new SqlParameter("@so", So), new SqlParameter("@Loai", Loai), new SqlParameter("@TuNgay", TuNgay), new SqlParameter("@DenNgay", DenNgay));
    }


    public DataSet dsHoaDonChuaIn(ref string error, string Sitecode)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetHoDonChuaInTVV, CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode));
    }

    public DataSet dsInfoHoaDonChuaIn(ref string error, string MaHD)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetInfoHoaDonChuaIn, CommandType.StoredProcedure, new SqlParameter("@tranno", MaHD));
    }
    public DataSet dsInfoHoaDonTraHang(ref string error, string MaHD)
    {
        return new DAL().MyExecuteQuery(ref error, "get_InfoHDTraHang", CommandType.StoredProcedure, new SqlParameter("@tranno", MaHD));
    }

    public DataSet dsMinDay(ref string error, string Sitecode)
    {
        return new DAL().MyExecuteQuery(ref error, sqlGetMinDay, CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode));
    }

    public DataSet dsInfoMatHang(ref string error, string MaHang)
    {
        return new DAL().MyExecuteQuery(ref error, sqlGetMatHang, CommandType.StoredProcedure, new SqlParameter("@MaHang", MaHang));
    }


    public bool InsertHoaDonBanHangTVV(ref string error, string Sitecode, string MaHD, string MaTVV, string UserName, double TongHoaDon, string NgayHD, DateTime DateTimeNow, int so)
    {
        return new DAL().MyExecuteNonQuery(SqlInsertHDBanHangTVV, CommandType.StoredProcedure, ref error,
            new SqlParameter("@Sitecode", Sitecode),
            new SqlParameter("@tranno", MaHD),
            new SqlParameter("@salesp_key", MaTVV),
            new SqlParameter("@Recuser", UserName),
            new SqlParameter("@doctot", TongHoaDon),
            new SqlParameter("@invdate", NgayHD),
            new SqlParameter("@Recdate", DateTimeNow),
            new SqlParameter("@so",so)
            );        
    }
    public bool InsertHoaDonUngHangChoNhomTruong(ref string error, string Sitecode, string MaHD, string MaTVV, string UserName, float TongHoaDon, string NgayHD, DateTime DateTimeNow)
    {
        return new DAL().MyExecuteNonQuery(SqlInsertHDUngHangChoNhomTruong, CommandType.StoredProcedure, ref error,
            new SqlParameter("@Sitecode", Sitecode),
            new SqlParameter("@tranno", MaHD),
            new SqlParameter("@salesp_key", MaTVV),
            new SqlParameter("@Recuser", UserName),
            new SqlParameter("@doctot", TongHoaDon),
            new SqlParameter("@invdate", NgayHD),
            new SqlParameter("@Recdate", DateTimeNow)
            );
    }
    public bool InsertHoaDonTraHangCuaNhomTruong(ref string error, string Sitecode, string MaHD, string MaTVV, string UserName, float TongHoaDon, string NgayHD, DateTime DateTimeNow)
    {
        return new DAL().MyExecuteNonQuery("Insert_HoaDonTraHangTVV", CommandType.StoredProcedure, ref error,
            new SqlParameter("@Sitecode", Sitecode),
            new SqlParameter("@tranno", MaHD),
            new SqlParameter("@salesp_key", MaTVV),
            new SqlParameter("@Recuser", UserName),
            new SqlParameter("@doctot", TongHoaDon),
            new SqlParameter("@invdate", NgayHD),
            new SqlParameter("@Recdate", DateTimeNow)
            );
    }

    public bool InsertCTHoaDonBanHangTVV(ref string error, string Sitecode, string MaHD,DateTime DateTimeNow, string UserName, string NgayHD, string MaHang, string TenHang, string LoaiDong, string MaVAT, float SoLuong, float Gia, int SoTT)
    {
        return new DAL().MyExecuteNonQuery(SqlInsertCTHDBanHangTVV, CommandType.StoredProcedure, ref error,
            new SqlParameter("@Sitecode", Sitecode),
            new SqlParameter("@tranno", MaHD),
            new SqlParameter("@Recdate", DateTimeNow),
            new SqlParameter("@Recuser", UserName),
            new SqlParameter("@invdate", NgayHD),            
            new SqlParameter("@itemkey", MaHang),
            new SqlParameter("@description", TenHang),
            new SqlParameter("@lineitemtyp", LoaiDong),
            new SqlParameter("@saletaxkey", MaVAT),
            new SqlParameter("@shipqty", SoLuong),
            new SqlParameter("@unitprice", Gia),
            new SqlParameter("@SoTT", SoTT)
            );
    }

    public bool InsertCTHoaDonTraHangTVV(ref string error, string Sitecode, string MaHD, DateTime DateTimeNow, string UserName, string NgayHD, string MaHang, string TenHang, string LoaiDong, string MaVAT, float SoLuong, float Gia, int SoTT)
    {
        return new DAL().MyExecuteNonQuery("Insert_CTHoaDonTraHangTVV", CommandType.StoredProcedure, ref error,
            new SqlParameter("@Sitecode", Sitecode),
            new SqlParameter("@tranno", MaHD),
            new SqlParameter("@Recdate", DateTimeNow),
            new SqlParameter("@Recuser", UserName),
            new SqlParameter("@invdate", NgayHD),
            new SqlParameter("@itemkey", MaHang),
            new SqlParameter("@description", TenHang),
            new SqlParameter("@lineitemtyp", LoaiDong),
            new SqlParameter("@saletaxkey", MaVAT),
            new SqlParameter("@shipqty", SoLuong),
            new SqlParameter("@unitprice", Gia),
            new SqlParameter("@SoTT", SoTT)
            );
    }

    public bool DeleteCTHoaDonBanHangTVV(ref string error, string MaHD)
    {
        return new DAL().MyExecuteNonQuery(SqlDeleteCTHDBanHangTVV, CommandType.StoredProcedure, ref error,
            new SqlParameter("@tranno", MaHD)
            );
    }
    public bool DeleteHoaDonBanHangTVV(ref string error, string MaHD)
    {
        return new DAL().MyExecuteNonQuery(SqlDeleteHDBanHangTVV, CommandType.StoredProcedure, ref error,
            new SqlParameter("@tranno", MaHD)
            );
    }

    public bool IsExistMaHD(ref string error, string MaHD)
    {
        return new DAL().IsExistFromSQL(SqlCheckMaHD, System.Data.CommandType.StoredProcedure, ref error, new SqlParameter("@tranno", MaHD));
    }
    public bool IsExistTVV(ref string error, string MaTVV)
    {
        return new DAL().IsExistFromSQL("IsExistMaTVV", System.Data.CommandType.StoredProcedure, ref error, new SqlParameter("@MaTVV", MaTVV));
    }

    public bool InsertUserInput(ref string error, string UserName, string MaHang, float SoLuong, double ThanhTien, float Gia, string MaVAT, int count)
    {
        return new DAL().MyExecuteNonQuery(SqlInsertUserInput, CommandType.StoredProcedure, ref error,
            new SqlParameter("@username", UserName),            
            new SqlParameter("@Itemkey", MaHang),
            new SqlParameter("@Quantity", SoLuong),
            new SqlParameter("@amount", ThanhTien),
            new SqlParameter("@IPrice", Gia),
            new SqlParameter("@IVAT", MaVAT),
            new SqlParameter("@count", count)
            );
    }

    public bool get_VuotTon(ref string error, ref string output , string MaHang,int SoLuong, string Location )
    { 
        return new DAL().MyExecuteNonQuery(SqlGetVuotTon, CommandType.StoredProcedure, ref error,ref output,"@VuotTon",SqlDbType.Int,
            new SqlParameter("@ItemKey", MaHang),
            new SqlParameter("@Location", Location),
            new SqlParameter("@SoXuat",SoLuong));
    }
    public string getMaKho(ref string error, string Sitecode)
    {
        string Location = "";
        try
        {
            Location = new DAL().MyExecuteQuerys(ref error, SqlGetLocation, CommandType.StoredProcedure, new SqlParameter("@Sitecode", Sitecode));
            return Location;
        }
        catch { return Location; }        
    }
    public DataSet dsHangKhuyenMai(ref string error, string UserName)
    {
        return new DAL().MyExecuteQuery(ref error, SqlGetListHangKhuyenMai, CommandType.StoredProcedure, new SqlParameter("@UserName", UserName));
    }
    public DataSet dsChiTietHoaDon(ref string error, string MaHD)
    {
        return new DAL().MyExecuteQuery(ref error, sqlGetChiTietDonHang, CommandType.StoredProcedure, new SqlParameter("@tranno", MaHD));
    }
    public DataSet dsChiTietHoaDonTra(ref string error, string MaHD)
    {
        return new DAL().MyExecuteQuery(ref error, "get_ChiTietHoaDonTra", CommandType.StoredProcedure, new SqlParameter("@tranno", MaHD));
    }

    public DataSet dsChiTietHoaDonPrint(ref string error, string MaHD)
    {
        return new DAL().MyExecuteQuery(ref error, sqlGetChiTietDonHangPrint, CommandType.StoredProcedure, new SqlParameter("@tranno", MaHD));
    }

    public bool DeleteHoaDonVaCTHDBanHangTVV(ref string error, string MaHD)
    {
        return new DAL().MyExecuteNonQuery(SqlDeleteHDvaCTHD, CommandType.StoredProcedure, ref error,
            new SqlParameter("@tranno", MaHD)
            );
    }
    
    public bool UpdateHoaDonTVV(ref string error, string MaHD, float TongHoaDon, string NgayHoaDon, DateTime DateTimeNow)
    {
        return new DAL().MyExecuteNonQuery(SqlUpdateHDTVV, CommandType.StoredProcedure, ref error,
            new SqlParameter("@tranno", MaHD), new SqlParameter("@doctot", TongHoaDon), new SqlParameter("@invdate", NgayHoaDon), new SqlParameter("@Recdate",DateTimeNow));
    }
    public bool UpdateChiTietHDTVV(ref string error, string MaHD, DataTable tableValue)
    { 
        string[] ParamName= new string[]{"@Sitecode","@tranno","@Recdate","@Recuser","@invdate","@itemkey","@description","@lineitemtyp","@saletaxkey","@shipqty","@unitprice","@SoTT"};
        string[] ColumnName= new string[]{"Sitecode","MaHD","DateTimeNow","UserName","NgayHD","MaHang","TenHang","LoaiDong","MaVAT","SoLuong","Gia","STT"};
        return new DAL().MyExecuteNonQueryWithTransaction(ref error, SqlDeleteCTHDBanHangTVV, SqlInsertCTHDBanHangTVV, CommandType.StoredProcedure,
            ParamName, tableValue, ColumnName, new SqlParameter("@tranno", MaHD));
    }

    public bool UpdateChiTietHDTraTVV(ref string error, string MaHD, DataTable tableValue)
    {
        string[] ParamName = new string[] { "@Sitecode", "@tranno", "@Recdate", "@Recuser", "@invdate", "@itemkey", "@description", "@lineitemtyp", "@saletaxkey", "@shipqty", "@unitprice", "@SoTT" };
        string[] ColumnName = new string[] { "Sitecode", "MaHD", "DateTimeNow", "UserName", "NgayHD", "MaHang", "TenHang", "LoaiDong", "MaVAT", "SoLuong", "Gia", "STT" };
        return new DAL().MyExecuteNonQueryWithTransaction(ref error, SqlDeleteCTHDBanHangTVV, "Insert_CTHoaDonTraHangTVV", CommandType.StoredProcedure,
            ParamName, tableValue, ColumnName, new SqlParameter("@tranno", MaHD));
    }

    public bool IsExistKhuyenMai(ref string error, string MaKM, string NgayHD)
    {
        return new DAL().IsExistFromSQL(sqlCheckIsExistKhuyenMai, CommandType.StoredProcedure, ref error,new SqlParameter("@Program_ID", MaKM), new SqlParameter("@NgayHD", NgayHD));
    }

    public bool UpdateHoaDonChuaIn(ref string error, string MaHD)
    {
        return new DAL().MyExecuteNonQuery(sqlUpdateHDChuaIn, CommandType.StoredProcedure, ref error,
            new SqlParameter("@tranno", MaHD));
    }
}
